/*
* 文 件 名:  InitData.java
* 版    权:  Tydic Technologies Co., Ltd. Copyright 1993-2012,  All rights reserved
* 描    述:  <描述>
* 修 改 人:  agan
* 修改时间:  2012-9-4
* 跟踪单号:  <跟踪单号>
* 修改单号:  <修改单号>
* 修改内容:  <修改内容>
*/

package com.tydic.simple.dao;

import com.ibatis.sqlmap.client.SqlMapClient;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

/**
* <一句话功能简述>
* <功能详细描述>
* 
* @author  agan
* @version  [版本号, 2012-9-4]
* @see  [相关类/方法]
* @since  [产品/模块版本]
*/

public class InitData {
    
    private SqlMapClient sqlmapClient;
    
    //用户表
    private static final String CREATE_TABLE_USER = "create table USER  (" 
        + "USER_ID      NUMERIC   not null,"
        + "USERNAME     VARCHAR2(32)," 
        + "PASSWORD     VARCHAR2(32)," 
        + "EMAIL        VARCHAR2(64),"
        + "TEL          VARCHAR2(16)," 
        + "LOGIN_ID     VARCHAR2(32)," 
        + "SEX          VARCHAR2(8),"
        + "STATUS       VARCHAR2(8),"
        + "ADDRESS      VARCHAR2(256),"
        + "BIRTHDAY     TIMESTAMP,"
        + "LATNNO       VARCHAR2(16),"
        + "LAST_LOGIN_TIME       TIMESTAMP,"
        + "CREATE_TIME  TIMESTAMP,"
        + "UPDATE_TIME  TIMESTAMP," 
        + "constraint PK_USER primary key (USER_ID)" + ")";
    private static final String CREATE_SEQ_USER = "create sequence SEQ_USER";
    private static final String INSERT_USER =
        "INSERT INTO USER(    USER_ID,    USERNAME,    PASSWORD,    EMAIL,    TEL,    LOGIN_ID, SEX, STATUS, ADDRESS, BIRTHDAY, LATNNO, LAST_LOGIN_TIME, CREATE_TIME,    UPDATE_TIME)   VALUES(    ?,    ?,    ?,    ?,    ?,    ?,  ?, ?, ?,? , ?, ?, sysdate,    sysdate)";
    
    
    
    //角色表
    private static final String CREATE_TABLE_ROLE = "create table ROLE  ("
        + "ROLE_ID              NUMERIC(10)                     not null,"
        + "ROLE_CODE            VARCHAR2(16)                    not null,"
        + "NAME                 VARCHAR2(32)                    not null,"
        + "DESCRIPTION          VARCHAR2(64),"
        + "REMARK               VARCHAR2(64),"
        + "STATUS               VARCHAR2(8),"
        + "CREATE_TIME          TIMESTAMP,"
        + "UPDATE_TIME          TIMESTAMP,"
        + "HOLD1                VARCHAR2(64),"
        + "HOLD2                VARCHAR2(64),"
        + "HOLD3                VARCHAR2(64),"
        + "constraint PK_ROLE primary key (ROLE_ID) )";
    private static final String CREATE_SEQ_ROLE = "create sequence SEQ_ROLE";
    private static final String INSERT_ROLE =
        "INSERT INTO ROLE(    ROLE_ID,    ROLE_CODE,    NAME,    DESCRIPTION,    REMARK,    STATUS, CREATE_TIME, UPDATE_TIME, HOLD1, HOLD2, HOLD3)   " +
                    "VALUES(    ?,    ?,    ?,    ?,    ?,    ?,  sysdate,    sysdate , ?,  ?,  ?)";
    
    
    //应用系统表
    private static final String CREATE_TABLE_APPLICATION = "create table APPLICATION  ("
       + "APP_ID               NUMERIC(10)                     not null,"
       + "APP_CODE             VARCHAR2(16)                    not null,"
       + "NAME                 VARCHAR2(64)                    not null,"
       + "ICON                 VARCHAR2(256),"
       + "DESCRIPTION          VARCHAR2(256),"
       + "REMARK               VARCHAR2(64),"
       + "STATUS               VARCHAR2(8),"
       + "CREATE_TIME          TIMESTAMP,"
       + "UPDATE_TIME          TIMESTAMP,"
       + "HOLD1                VARCHAR2(64),"
       + "HOLD2                VARCHAR2(64),"
       + "constraint PK_APPLICATION primary key (APP_ID)"
    + ")"  ;
    private static final String CREATE_SEQ_APPLICATION = "create sequence SEQ_APPLICATION";
    private static final String INSERT_APPLICATION = "INSERT INTO APPLICATION( APP_ID, APP_CODE,  NAME, ICON, DESCRIPTION, REMARK, STATUS, HOLD1,  HOLD2,  CREATE_TIME, UPDATE_TIME)"
        + "VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate, sysdate)";
    
    
    
    //部门表
    private static final String CREATE_TABLE_DEPARTMENT = "create table DEPARTMENT  ("
    		 + "DEP_ID               NUMERIC(10)                     not null,"
 		    + "DEP_CODE             VARCHAR2(16)                    not null,"
 		    + "NAME                 VARCHAR2(32)                    not null,"
 		    + "DESCRIPTION          VARCHAR2(64),"
 		    + "REMARK               VARCHAR2(64),"
 		    + "STATUS               VARCHAR2(8),"
 		    + "PARENT_DEP_CODE      VARCHAR2(16),"
 		    + "CREATE_TIME          TIMESTAMP,"
 		    + "UPDATE_TIME          TIMESTAMP,"
 		   + " HOLD1                VARCHAR2(64),"
 		    + "HOLD2                VARCHAR2(64),"
 		   + "HOLD3                VARCHAR2(64),"
 		    + "constraint PK_DEPARTMENT primary key (DEP_ID)"
 		 + ")";
     private static final String CREATE_SEQ_DEPARTMENT = "create sequence SEQ_DEPARTMENT";
     private static final String INSERT_DEPARTMENT = "INSERT INTO DEPARTMENT( DEP_ID, DEP_CODE, NAME, DESCRIPTION, REMARK, STATUS, PARENT_DEP_CODE, HOLD1, HOLD2, HOLD3, CREATE_TIME, UPDATE_TIME)"
    	    		+ "VALUES( ?, ? , ?, ?, ?, ?, ?, '', '', '', sysdate, sysdate)";
     
     
     //字典表
     private static final String CREATE_TABLE_DICTIONARY = "create table DICTIONARY  ("
         + "DIC_ID               NUMERIC(10)                     not null,"
         + "DIC_CODE             VARCHAR2(16)                    not null,"
         + "NAME                 VARCHAR2(64)                    not null,"
         + "VALUE                VARCHAR2(256)                   not null,"
         + "ORDER_NO             NUMERIC(10),"
         + "TYPE                 VARCHAR2(16),"
         + "STATUS               VARCHAR2(8),"
         + "REMARK               VARCHAR2(64),"
         + "HOLD1                VARCHAR2(64),"
         + "HOLD2                VARCHAR2(64),"
         + "CREATE_TIME          TIMESTAMP,"
         + "UPDATE_TIME          TIMESTAMP,"
         + "constraint PK_DICTIONARY primary key (DIC_ID)"
      + ")";
     private static final String CREATE_SEQ_DICTIONARY = "create sequence SEQ_DICTIONARY";
     private static final String INSERT_DICTIONARY = "INSERT INTO DICTIONARY( DIC_ID, DIC_CODE, NAME, VALUE, ORDER_NO,  TYPE, STATUS,  REMARK,  HOLD1, HOLD2, CREATE_TIME, UPDATE_TIME)"
         + " VALUES( ? , ? , ? , ?, ?,  ?,  ? , ? ,  '' , '', sysdate,  sysdate)";
     
     
     //权限表
     private static final String CREATE_TABLE_POPEDOM = "create table POPEDOM  ("
         + "POPEDOM_ID           NUMERIC(16)                     not null,"
         + "POPEDOM_CODE         VARCHAR2(32),"
          + "NAME                 VARCHAR2(64),"
          + "RES_CODE             VARCHAR2(32),"
          + "POPEDOM_URL          VARCHAR2(256),"
          + "DESCRIPTION          VARCHAR2(256),"
          + "REMARK               VARCHAR2(64),"
          + "CREATE_TIME          TIMESTAMP,"
          + "UPDATE_TIME          TIMESTAMP,"
          + "constraint PK_POPEDOM primary key (POPEDOM_ID)"
       + ")";
     private static final String CREATE_SEQ_POPEDOM = "create sequence SEQ_POPEDOM";
     private static final String INSERT_POPEDOM = "INSERT INTO POPEDOM( POPEDOM_ID,  POPEDOM_CODE, NAME, RES_CODE, POPEDOM_URL, DESCRIPTION,  REMARK, CREATE_TIME, UPDATE_TIME)"
        +" VALUES( ? , ? ,  ? ,  ? ,  ? ,  ?, ?, sysdate,  sysdate)";
     
     
     //资源表
     private static final String CREATE_TABLE_RESOURCE = "create table RESOURCE  ("
         +" RES_ID               NUMERIC(10)                     not null,"
         +" RES_CODE             VARCHAR2(32)                    not null,"
         +" APP_CODE             VARCHAR2(16)                    not null,"
         +" NAME                 VARCHAR2(64),"
         +" URL                  VARCHAR2(256),"
         +" TYPE                 VARCHAR2(16),"
         +" ICON                 VARCHAR2(256),"
         +" ORDER_NO             NUMERIC(10),"
         +" REMARK               VARCHAR2(64),"
         +" PARENT_RES_CODE      VARCHAR2(32),"
         +" STATUS               VARCHAR2(8),"
         +" CREATE_TIME          TIMESTAMP,"
         +" UPDATE_TIME          TIMESTAMP,"
         +" HOLD1                VARCHAR2(64),"
         +" HOLD2                VARCHAR2(64),"
         +" constraint PK_RESOURCE primary key (RES_ID)"
      +" )";
     private static final String CREATE_SEQ_RESOURCE = "create sequence SEQ_RESOURCE";
     private static final String INSERT_RESOURCE = "INSERT INTO RESOURCE( RES_ID,  RES_CODE,  APP_CODE, NAME,  URL, TYPE,"
            + " ICON,  ORDER_NO,  REMARK, PARENT_RES_CODE, STATUS, HOLD1, HOLD2, CREATE_TIME, UPDATE_TIME)"
        + " VALUES(  ? , ? , ?,  ?, ?,  ?,  ?,  ?,   ?, ?,  ?,  '',  '',  sysdate,  sysdate)";
    	    

    
    
  
    public InitData(SqlMapClient sqlmapClient){
        this.sqlmapClient = sqlmapClient;
    }
    public void init(){
        initSeq();
        initTable();
        initData();
    }
    /**
     * <一句话功能简述>
     * <功能详细描述>
     * @param sqlmapClient
     * @see [类、类#方法、类#成员]
     */
    public void initTable() {
        try {
            Connection conn = sqlmapClient.getDataSource().getConnection();
            conn.setAutoCommit(true);
            Statement stm = conn.createStatement();
            stm.execute(CREATE_TABLE_USER);
            stm.execute(CREATE_TABLE_ROLE);
            stm.execute(CREATE_TABLE_APPLICATION);
            stm.execute(CREATE_TABLE_DEPARTMENT);
            stm.execute(CREATE_TABLE_DICTIONARY);
            stm.execute(CREATE_TABLE_POPEDOM);
            stm.execute(CREATE_TABLE_RESOURCE);
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    /**
     * <一句话功能简述>
     * <功能详细描述>
     * @param sqlmapClient
     * @see [类、类#方法、类#成员]
     */
    public void initSeq() {
        try {
            Connection conn = sqlmapClient.getDataSource().getConnection();
            conn.setAutoCommit(true);
            Statement stm = conn.createStatement();
            stm.execute(CREATE_SEQ_USER);
            stm.execute(CREATE_SEQ_ROLE);
            stm.execute(CREATE_SEQ_APPLICATION);
            stm.execute(CREATE_SEQ_DEPARTMENT);
            stm.execute(CREATE_SEQ_DICTIONARY);
            stm.execute(CREATE_SEQ_POPEDOM);
            stm.execute(CREATE_SEQ_RESOURCE);
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    /**
     * <一句话功能简述>
     * <功能详细描述>
     * @param sqlmapClient
     * @see [类、类#方法、类#成员]
     */
    public void initView() {
        
    }
    
    /**
     * <一句话功能简述>
     * <功能详细描述>
     * @param sqlmapClient
     * @see [类、类#方法、类#成员]
     */
    public void initData() {
        Connection conn =  null;
        PreparedStatement stm = null;
        
        try {
            conn = sqlmapClient.getDataSource().getConnection();
            conn.setAutoCommit(true);
            stm = conn.prepareStatement(INSERT_USER);
            stm.setLong(1, 10000L);
            stm.setString(2, "zhouliang");
            stm.setString(3, "password");
            stm.setString(4, "zhouliang1@tydic.com");
            stm.setString(5, "15818774641");
            stm.setString(6, "D3349");
            stm.setString(7, "男");
            stm.setString(8, "enable");
            stm.setString(9, "湖南岳阳AAAAAAAAAA");
            stm.setDate(10, new java.sql.Date(new java.util.Date().getTime()));
            stm.setString(11, "湖南岳阳");
            stm.setString(12, null);
            stm.execute();
            
            stm = conn.prepareStatement(INSERT_USER);
            stm.setLong(1, 10001L);
            stm.setString(2, "zhouyang");
            stm.setString(3, "password");
            stm.setString(4, "zhouyang@tydic.com");
            stm.setString(5, "1302342342342");
            stm.setString(6, "D3300");
            stm.setString(7, "男");
            stm.setString(8, "enable");
            stm.setString(9, "AAAAAAAAAA");
            stm.setDate(10, new java.sql.Date(new java.util.Date().getTime()));
            stm.setString(11, "湖南岳阳天油压");
            stm.setString(12, null);
            stm.execute();
            conn.commit();
            stm.close();
            
            
           // "INSERT INTO ROLE(    ROLE_ID,    ROLE_CODE,    NAME,    DESCRIPTION,    REMARK,    STATUS, CREATE_TIME, UPDATE_TIME, HOLD1, HOLD2, HOLD3)   " +
            //"VALUES(    ?,    ?,    ?,    ?,    ?,    ?,  sysdate,    sysdate , ?,  ?,  ?)";
            stm = conn.prepareStatement(INSERT_ROLE);
            stm.setLong(1, 10000L);
            stm.setString(2, "admin_code");
            stm.setString(3, "admin");
            stm.setString(4, "admin description");
            stm.setString(5, "remark");
            stm.setString(6, "enable");
            stm.setString(7, "hold1");
            stm.setString(8, "hold2");
            stm.setString(9, "hold3");
            stm.execute();
            stm.setLong(1, 10001L);
            stm.setString(2, "admin_code1");
            stm.setString(3, "admin1");
            stm.setString(4, "admin description1");
            stm.setString(5, "remark1");
            stm.setString(6, "enable1");
            stm.setString(7, "hold11");
            stm.setString(8, "hold21");
            stm.setString(9, "hold31");
            stm.execute();
            conn.commit();
            
            
            //INSERT_APPLICATION = "INSERT INTO APPLICATION( APP_ID, APP_CODE,  NAME, ICON, DESCRIPTION, REMARK, STATUS, HOLD1,  HOLD2,  CREATE_TIME, UPDATE_TIME)"
            //+ "VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate, sysdate)";
            stm = conn.prepareStatement(INSERT_APPLICATION);
            stm.setLong(1, 10000L);
            stm.setString(2, "app_code");
            stm.setString(3, "电子商城");
            stm.setString(4, "/path/a.jpg");
            stm.setString(5, "description");
            stm.setString(6, "remark");
            stm.setString(7, "status");
            stm.setString(8, "hold1");
            stm.setString(9, "hold2");
            stm.execute();
            conn.commit();
            
            
            //private static final String INSERT_DEPARTMENT = "INSERT INTO DEPARTMENT( DEP_ID, DEP_CODE, NAME, DESCRIPTION, REMARK, STATUS, PARENT_DEP_CODE, HOLD1, HOLD2, HOLD3, CREATE_TIME, UPDATE_TIME)"
    	    		//+ "VALUES( ?, ? , ?, ?, ?, ?, ?, '', '', '', sysdate, sysdate)";
            stm = conn.prepareStatement(INSERT_DEPARTMENT);
            stm.setLong(1, 10000L);
            stm.setString(2, "DEV_CODE_5");
            stm.setString(3, "研发五部");
            stm.setString(4, "description");
            stm.setString(5, "remark");
            stm.setString(6, "disable");
            stm.setLong(7, -1l);
            stm.execute();
            conn.commit();
            
            //private static final String INSERT_DICTIONARY = "INSERT INTO DICTIONARY( DIC_ID, DIC_CODE, NAME, VALUE, ORDER_NO,  TYPE, STATUS,  REMARK,  HOLD1, HOLD2, CREATE_TIME, UPDATE_TIME)"
            //+ " VALUES( ? , ? , ? , ?, ?,  ?,  ? , ? ,  '' , '', sysdate,  sysdate)";
            stm = conn.prepareStatement(INSERT_DICTIONARY);
            stm.setLong(1, 10000L);
            stm.setString(2, "LOCAL_CODE_550");
            stm.setString(3, "安徽电信");
            stm.setString(4, "550");
            stm.setInt(5, 0);
            stm.setString(6, "LOCAL_CODE");
            stm.setString(7, "enable");
            stm.setString(8, "无");
            stm.execute();
            conn.commit();
            
            
            //private static final String INSERT_POPEDOM = "INSERT INTO POPEDOM( POPEDOM_ID,  POPEDOM_CODE, NAME, RES_CODE, POPEDOM_URL, DESCRIPTION,  REMARK, CREATE_TIME, UPDATE_TIME)"
                //+" VALUES( ? , ? ,  ? ,  ? ,  ? ,  ?, ?, sysdate,  sysdate)";
            stm = conn.prepareStatement(INSERT_POPEDOM);
            stm.setLong(1, 10000L);
            stm.setString(2, "PRODUCT_ADD");
            stm.setString(3, "增加产品");
            stm.setString(4, "PRODUCT_RES");
            stm.setString(5, "http://adfadfaf");
            stm.setString(6, "description");
            stm.setString(7, "remark");
            stm.execute();
            conn.commit();
            
            
            //private static final String INSERT_RESOURCE = "INSERT INTO RESOURCE( RES_ID,  RES_CODE,  APP_CODE, NAME,  URL, TYPE,"
                //+ " ICON,  ORDER_NO,  REMARK, PARENT_RES_CODE, STATUS, HOLD1, HOLD2, CREATE_TIME, UPDATE_TIME)"
            //+ " VALUES(  ? , ? , ?,  ?, ?,  ?,  ?,  ?,   ?, ?,  ?,  '',  '',  sysdate,  sysdate)";
            stm = conn.prepareStatement(INSERT_RESOURCE);
            stm.setLong(1, 10000L);
            stm.setString(2, "PRODUCT_RES");
            stm.setString(3, "PRODUCT_SYS");
            stm.setString(4, "产品管理");
            stm.setString(5, "http://www.163.com");
            stm.setString(6, "MENU");
            stm.setString(7, "test.ico");
            stm.setInt(8, 0);
            stm.setString(9, "remark");
            stm.setString(10, "");
            stm.setString(11, "enable");
            stm.execute();
            stm.setLong(1, 10001L);
            stm.setString(2, "PRODUCT_RES1");
            stm.setString(3, "PRODUCT_SYS1");
            stm.setString(4, "产品管理1");
            stm.setString(5, "http://www.163.com1");
            stm.setString(6, "MENU1");
            stm.setString(7, "test.ico1");
            stm.setInt(8, 0);
            stm.setString(9, "remark1");
            stm.setString(10, "");
            stm.setString(11, "enable1");
            stm.execute();
            conn.commit();
            
            
            
            stm.close();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }finally{
            try {
                conn.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
